package cn.com.nes.site.module.dao.newmall;

import cn.com.nes.common.util.*;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.PageData;
import cn.com.nes.site.module.dao.BaseDaoImpl;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionStatus;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

@Repository
public class NewmallDaoImpl extends BaseDaoImpl {

    private TransactionStatus newg_status = null;

    public PageData getAgentAndShopkey(Connection conn,DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        param.put("tableName", "newshop_user");
        try {
            //根据uid去商城查询到手机号
            String phone = getShopUserPhone((String)param.get("uid"));
            param.put("phone", phone);
            //根据phone查询信息
            StringBuffer sb = new StringBuffer();
            sb.append(" select * from newshop_user where isdelete=0 and phone=? ");
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[] {param.get("phone")});
            if (list.size() == 0) {
                param.put("isLog","0");
                res =  insertTableData(conn, req) ;
                conn.commit();
            }
            // 生成jwt信息
            res = getUserInfo(phone);
            JwtUtil util = new JwtUtil();
            String key = util.createJWT((String)param.get("phone"),JsonUtil.getStriFromObj(res.getData()), Globals.TOKEN_TIME);
            res.getData().put("key", key);
        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public String getShopUserPhone(String uid) {
        String phone="";
        Connection conn_newmall = null;
        try {
            conn_newmall = DBUitl.getDBConn_newmall();
            String sb = " select * from eb_user where id = ? ";
            List<Map<String, Object>> listTmp = this.queryForList(conn_newmall, sb.toString(), new Object[]{uid});
            if (listTmp.size() > 0) {//如果有添加用户信息
                phone=listTmp.get(0).get("phone").toString();
            }
        } catch (Exception ignored) {
            ignored.printStackTrace();
        } finally {
            try {
                conn_newmall.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return phone;
    }

    public PageData checkVerCodeIncreaseTime(String phone,String vercode)  {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("验证码不正确！");
        StringBuffer sb = new StringBuffer();
        try {
            sb.append("select TIMESTAMPDIFF(SECOND,createTime,SYSDATE()) as seconds,verCode from t_sms where phone=? and vercode=?");
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[] { phone,vercode});
            if (list.size() > 0) {
                Long seconds = (Long) list.get(0).get("seconds");
                if (seconds > 1000 * 60 * 30) {
                    res.setRetMessage("验证码过期,请重新获取!");
                } else {
                    res.setRetCode(0);
                    res.setRetMessage("OK");
                }
            }
        } finally {
        }
        return res;
    }

    public PageData login(Connection conn,DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        param.put("tableName", "newshop_user");
        try {
            StringBuffer sb = new StringBuffer();
            sb.append(" select * from newshop_user where isdelete=0 and phone=? ");
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[] {param.get("phone")});
            if (list.size() == 0) {
                param.put("isLog","0");
                res =  insertTableData(conn, req) ;
                conn.commit();
            }
            // 生成jwt信息
            res = getUserInfo(req);
            JwtUtil util = new JwtUtil();
            String key = util.createJWT((String)param.get("phone"),JsonUtil.getStriFromObj(res.getData()), Globals.TOKEN_TIME);
            res.getData().put("key", key);

            // 获取商城用户id
            String shopUserId = getShopUser((String)param.get("phone"));
            res.getData().put("shopUserId", shopUserId);

        } catch (Exception e) {
            res.setRetCode(9);
            res.setRetMessage("操作失败！");
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public String getShopUser(String phone) {
        String shopUserId="";
        Connection conn_newmall = null;
        try {
            conn_newmall = DBUitl.getDBConn_newmall();
            String sb = " select id from eb_user where phone = ? and is_logoff=0 ";
            List<Map<String, Object>> listTmp = this.queryForList(conn_newmall, sb.toString(), new Object[]{phone});
            if (listTmp.size() > 0) {//如果有添加用户信息
                shopUserId=listTmp.get(0).get("id").toString();
            } else {
                try {
                    //不存商城用户，请求商城接口，创建新用户
                    String sbadd =" INSERT INTO `eb_user`(`phone`,`account`,`pwd`) VALUES ('"+phone+"','app用户','123456') ";
                    newg_status = begin_mall();// 开启事物
                    this.getJdbcTemplate_mall().update(sbadd.toString(), new Object[]{});
                    commit_mall(newg_status);
                } catch (Exception e) {
                    rollback_mall(newg_status);
                    e.printStackTrace();
                }
                //注册完查询
                listTmp = this.queryForList(conn_newmall, sb.toString(), new Object[]{phone});
                shopUserId=listTmp.get(0).get("id").toString();
            }
        } catch (Exception ignored) {
            ignored.printStackTrace();
        } finally {
            try {
                conn_newmall.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return shopUserId;
    }

    public PageData getUserInfo(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取用户信息失败");
        try {
            StringBuffer sb = new StringBuffer();
            sb.append(" select * from newshop_user where phone=? and isdelete=0 ");
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[] {param.get("phone")});
            if (list.size() == 1) {
                res.setData(list.get(0));
                res.setRetCode(0);
                res.setRetMessage("OK");
            } else {
                res.setRetCode(9);
                res.setRetMessage("没有查询到该用户信息！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getUserInfo(String phone) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取用户信息失败");
        try {
            StringBuffer sb = new StringBuffer();
            sb.append(" select * from newshop_user where phone=? and isdelete=0 ");
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[] {phone});
            if (list.size() == 1) {
                res.setData(list.get(0));
                res.setRetCode(0);
                res.setRetMessage("OK");
            } else {
                res.setRetCode(9);
                res.setRetMessage("没有查询到该用户信息！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getShopCompanyList(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取门店信息失败");
        try {
            //按距离搜索门店
            String longitude=req.getReqData().get("longitude").toString();
            String latitude=req.getReqData().get("latitude").toString();
            StringBuffer sb = new StringBuffer();
            StringBuffer sbfrom = new StringBuffer();
            sb.append(" select contactNumber,id,mer_id,mer_name,mer_scale,mer_business,address,longitude,latitude,ROUND(lat_lng_distance(?,?, latitude, longitude), 2) as distance,mer_logo " +
                    " from t_company where mer_id <> '' and mer_id is not null and mer_id <> '0' and isnewshop='1' ");
            if(!StringUtils.isEmpty((String)req.getReqData().get("keyword"))) {
                sb.append(" and ( ");
                sb.append(" mer_name like '%").append(req.getReqData().get("keyword")).append("%' ");
                sb.append(" or mer_scale like '%").append(req.getReqData().get("keyword")).append("%' ");
                sb.append(" or address like '%").append(req.getReqData().get("keyword")).append("%' ");
                sb.append(" or mer_business like '%").append(req.getReqData().get("keyword")).append("%' ");
                sb.append(" )");
            }
            sbfrom.append(sb.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(), req.getPageid(), req.getPagesize(), res,new Object[] {});
            sb.append(" ORDER BY distance asc ");
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[] {latitude,longitude});
            if(list.size()>0){
                //查询门店logo
                for(int j=0;j<list.size();j++){
                    String mer_logo=list.get(j).get("mer_logo").toString();
                    String mer_logoUrl=OssUtil.getImgSignURL(mer_logo,0);
                    list.get(j).put("mer_logoUrl",mer_logoUrl);
                }
            }
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getBrowseCompanyList(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取门店信息失败");
        try {
            //最近访问记录
            String longitude=req.getReqData().get("longitude").toString();
            String latitude=req.getReqData().get("latitude").toString();
            StringBuffer sb = new StringBuffer();
            String userid = req.getUinfo().getId(); // 用户ID
            sb.delete(0, sb.length());
            sb.append(" select b.contactNumber,b.id,b.mer_id,b.mer_name,b.mer_scale,b.mer_business,b.address,b.longitude,b.latitude,ROUND(lat_lng_distance(?,?, b.latitude, b.longitude), 2) as distance,mer_logo " +
                    " from t_company_user_browse a " +
                    " LEFT JOIN t_company b on a.companyId=b.id " +
                    " where a.sfzx='1' and a.userId=? " +
                    " ORDER BY a.createTime desc limit 6 ");
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{latitude,longitude,userid});
            if(list.size()>0){
                //查询门店logo
                for(int j=0;j<list.size();j++){
                    String mer_logo=(String)list.get(j).get("mer_logo");
                    String mer_logoUrl=OssUtil.getImgSignURL(mer_logo,0);
                    list.get(j).put("mer_logoUrl",mer_logoUrl);
                }
            }
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getShopCompanyById(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取门店信息失败");
        try {
            StringBuffer sb = new StringBuffer();
            String longitude=req.getReqData().get("longitude").toString();
            String latitude=req.getReqData().get("latitude").toString();
            sb.append(" select contactNumber,id,mer_id,mer_name,mer_scale,mer_business,mer_introduction,address,longitude,latitude,ROUND(lat_lng_distance(?,?, latitude, longitude), 2) as distance," +
                    "mer_timeStart,mer_timeEnd,if(date_format(now(),'%H:%i') BETWEEN mer_timeStart and mer_timeEnd,'营业中','休息中') mer_yezt,mer_img " +
                    "from t_company where mer_id <> '' and mer_id is not null and mer_id <> '0' and id=? ");
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{latitude,longitude,req.getReqData().get("id")});
            if(list.size()>0){
                //查询门店图片
                String mer_img=list.get(0).get("mer_img").toString();
                String[] imgArr=mer_img.split(";");
                if(imgArr.length>0){
                    ArrayList<Object> imgUrlMap=new ArrayList<Object>();
                    for(int i=0;i<imgArr.length;i++){
                        String imgName=imgArr[i];
                        String imgUrl=OssUtil.getImgSignURL(imgName,0);
                        Map<String, String> imgMap=new HashMap<String,String>();
                        imgMap.put("name", imgName);
                        imgMap.put("url", imgUrl);
                        imgUrlMap.add(i, imgMap);
                    }
                    list.get(0).put("imgUrlMap",imgUrlMap);
                }
            }
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData userPraise(DataReq req) throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("点赞失败");
        StringBuffer sb = new StringBuffer();
        Connection conn= null ;
        try {
            conn = DBUtils.getJNDIDBConn();
            String userid = req.getUinfo().getId();	// 用户ID
            String praiseType = (String)req.getReqData().get("praiseType"); // 点赞类别，0-文章点赞，1-评论点赞
            String praiseId = (String)req.getReqData().get("praiseId"); // 点赞ID 根据点赞类别传入对应的ID
            String deleted = (String)req.getReqData().get("deleted"); // 删除标识，0-未删除，1-已删除
            Date recordDate = new Date();

            // 取消点赞，先删除之前的点赞记录
            if (deleted.equals("1")) {
                sb.delete(0, sb.length());
                sb.append("update user_praise_record set is_deleted = 1 where praise_type = ? and praise_id = ? and user_id = ?");
                this.update(conn,sb.toString(), new Object[]{praiseType, praiseId, userid});
            }

            // 新增点赞记录
            sb.delete(0, sb.length());
            sb.append("insert into user_praise_record (praise_type, praise_id, user_id, create_time, is_deleted) ");
            sb.append("values(?,?,?,?,?)");
            this.update(conn,sb.toString(), new Object[]{praiseType, praiseId, userid, recordDate, deleted});

            conn.commit();
            res.setRetCode(0);
            res.setRetMessage("点赞成功");
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return res;
    }

    public PageData userComment(DataReq req)  throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("评论失败");
        StringBuffer sb = new StringBuffer();
        Connection conn = null ;
        try {
            conn = DBUtils.getJNDIDBConn();
            String userid = req.getUinfo().getId(); // 用户ID
            String newsId = (String)req.getReqData().get("newsId"); // 文章ID
            String commentType = (String)req.getReqData().get("commentType"); // 评论类别：0-文章评论，1-回复评论
            String commentContent = (String)req.getReqData().get("commentContent"); // 评论内容
            String otherCommentId = (String)req.getReqData().get("otherCommentId"); // 他人评论ID
            Date recordDate = new Date();

            // 判断评论内容
            if (StrUtil.isBlank(commentContent)) {
                res.setRetCode(9);
                res.setRetMessage("评论内容不能为空");
                return res;
            }
            JSONObject jsonObject = BaiduAPI.checkComment(commentContent);
            String conclusion = jsonObject.getString("conclusion");
            if (conclusion.equals("不合规")) {
                res.setRetCode(9);
                res.setRetMessage("评论失败，评论内容存在敏感词汇！");
                return res;
            }
            if (StrUtil.isEmpty(otherCommentId)) {
                otherCommentId = null;
            }

            sb.delete(0, sb.length());
            sb.append("insert into user_comment_record (news_id, comment_user_id, comment_time, other_comment_id, comment_type, comment_content) ");
            sb.append("values(?,?,?,?,?,?)");
            this.update(conn,sb.toString(), new Object[]{newsId, userid, recordDate, otherCommentId, commentType, commentContent});

            conn.commit();
            res.setRetCode(0);
            res.setRetMessage("评论成功");
        } finally {
            if (conn!= null) {
                conn.close();
            }
        }

        return res;
    }

    public PageData deleteUserComment(DataReq req) throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("删除评论失败！");
        StringBuffer sb = new StringBuffer();
        Connection conn = null ;
        try {
            conn = DBUtils.getJNDIDBConn();
            String commentId = (String)req.getReqData().get("commentId"); // 评论ID

            sb.delete(0, sb.length());
            sb.append("update user_comment_record set is_deleted = 1 where id = ? or other_comment_id = ?");
            this.update(conn, sb.toString(), new Object[]{commentId, commentId});

            // 如果是一级评论，需要删除二级评论
			sb.delete(0, sb.length());
			sb.append("update user_comment_record set is_deleted = 1 where other_comment_id = ?");
            this.update(conn, sb.toString(), new Object[]{commentId});

            conn.commit();
            res.setRetCode(0);
            res.setRetMessage("ok");
        } finally {
            if (conn != null) {
                conn.close();
            }
        }

        return res;
    }

    public PageData getUserComment(DataReq req) throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询用户评论失败！");
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        HashMap<String, Object> resultMap = new HashMap<String, Object>();
        Connection conn = null ;
        try {
            conn = DBUtils.getJNDIDBConn();

            String newsId = (String)req.getReqData().get("newsId");

            // 查询文章评论
            sb.delete(0, sb.length());
            sb.append("select a.id,a.news_id,0 as isOpen,date_format(a.comment_time,'%Y-%m-%d %T') as time,a.comment_content as text,if(b.username is null, b.nickname, b.username) as name,");
            sb.append("b.PhotoUrl as headerPic,a.comment_user_id as commentUserId,");
            sb.append("(select count(1) from user_comment_record a1 where a1.is_deleted = 0 and a.id = a1.other_comment_id) as childrenCount,");
            sb.append("if(c.id is null , false, true) as praiseState,");
            sb.append("(select count(1) from user_praise_record c1 where c1.is_deleted = 0 and a.id = c1.praise_id and c1.praise_type = 1) as praiseNum ");

            //分页
            sbfrom.append(" from user_comment_record a left join newshop_user b on a.comment_user_id = b.id ");
            sbfrom.append(" left join user_praise_record c on a.comment_user_id = c.user_id and c.is_deleted = 0 and a.id = c.praise_id and c.praise_type = 1 ");
            sbfrom.append(" where a.is_deleted = 0 and a.news_id =? ");
            sbfrom.append(" and a.comment_type = 0 ");
            sbfrom.append(" order by a.comment_time desc");
            Page page = new Page(conn,sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{newsId}) ;
            sb.append(sbfrom);
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;

            List<Map<String, Object>> list = this.queryForList(conn,sb.toString(), new Object[] {newsId}) ;

            if(list.size()>0){
                for (Map<String, Object> map : list) {
                    // 获取评论记录ID
                    Long id = (Long) map.get("id");
                    String headerPic = (String) map.get("headerPic");
                    map.put("absolutePic", DBUitl.getImgvVideoSignURL(headerPic, 3));

                    sb.delete(0, sb.length());
                    sb.append("select a.id, date_format(a.comment_time,'%Y-%m-%d %T') as time,a.comment_content as text, if(b.username is null, b.nickname, b.username) as name,b.PhotoUrl as headerPic, a.comment_user_id as commentUserId ");
                    sb.append(" from user_comment_record a left join newshop_user b on a.comment_user_id = b.id ");
                    sb.append(" where a.is_deleted = 0 and a.comment_type = 1 and a.other_comment_id = ");
                    sb.append(id);
                    sb.append(" order by a.comment_time desc");

                    List<Map<String, Object>> list2 = this.queryForList(conn,sb.toString(), new Object[] {}) ;
                    List<Object> list3 = new ArrayList<Object>();
                    if (list2.size() > 0) {
                        Map<String, Object> map1 = list2.get(0);
                        String headerPic1 = (String) map1.get("headerPic");
                        map1.put("absolutePic", DBUitl.getImgvVideoSignURL(headerPic1, 3));
                        list3.add(map1);
                        map.put("children", list3);
                    } else {
                        map.put("children", list3);
                    }
                }
                res.setRetCode(0) ;
                res.setRetMessage("OK") ;
                res.setPage(list) ;
            } else {
                res.setRetCode(0) ;
                res.setRetMessage("OK") ;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return res;
    }

    public PageData getUserCommentSecond(DataReq req) throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询二级用户评论列表失败！");
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        HashMap<String, Object> resultMap = new HashMap<String, Object>();
        Connection conn = null ;
        try {
            conn = DBUtils.getJNDIDBConn();

            String commentId = req.getReqData().get("commentId").toString();
            String commentDate = req.getReqData().get("commentDate").toString();

            sb.delete(0, sb.length());
            sb.append("select a.id, date_format(a.comment_time,'%Y-%m-%d %T') as time,a.comment_content as text, if(b.username is null, b.nickname, b.username) as name,b.PhotoUrl as headerPic,a.comment_user_id as commentUserId ");
            sb.append(" from user_comment_record a left join newshop_user b on a.comment_user_id = b.id ");
            sb.append(" where a.is_deleted = 0 and a.comment_type = 1 and a.other_comment_id = ");
            sb.append(commentId);
            sb.append(" and a.comment_time < '");
            sb.append(commentDate);
            sb.append("' order by a.comment_time desc");

            //分页
            sbfrom.append(sb.toString()) ;
            int idx=sbfrom.indexOf(" from ") ;
            sbfrom.delete(0, idx);
            Page page = new Page(conn,sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{}) ;
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;

            List<Map<String, Object>> list = this.queryForList(conn,sb.toString(), new Object[]{});

            for (Map<String, Object> map : list) {
                String headerPic = (String) map.get("headerPic");
                map.put("absolutePic", DBUitl.getImgvVideoSignURL(headerPic, 3));
            }

            if(list.size()>0){
                res.setRetCode(0) ;
                res.setRetMessage("OK") ;
                res.setPage(list) ;
            } else {
                res.setRetCode(0) ;
                res.setRetMessage("OK") ;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return res;
    }

    public PageData getProductRecommend(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取商品推荐信息失败");
        Map<String,Object> extData = new LinkedHashMap<String, Object>();
        try {
            //轮播图
            StringBuffer sb = new StringBuffer();
            sb.append(" select mer_img from t_company where mer_id = ? ");
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{req.getReqData().get("mer_id")});
            ArrayList<Object> imgUrlMap=new ArrayList<Object>();
            if(list.size()>0){
                //查询门店图片
                String mer_img=list.get(0).get("mer_img").toString();
                String[] imgArr=mer_img.split(";");
                if(imgArr.length>0){
                    for(int i=0;i<imgArr.length;i++){
                        String imgName=imgArr[i];
                        String imgUrl=OssUtil.getImgSignURL(imgName,0);
                        Map<String, String> imgMap=new HashMap<String,String>();
                        imgMap.put("name", imgName);
                        imgMap.put("url", imgUrl);
                        imgUrlMap.add(i, imgMap);
                    }
                }
            }
            extData.put("topData", imgUrlMap) ;
            //推荐商品
            sb = new StringBuffer();
            sb.append(" SELECT id product_id,mer_id,`name` store_name,`intro` store_info,CONCAT('https://nes-mer.oss-cn-beijing.aliyuncs.com/',image) image,`sales` from eb_product where mer_id=? ORDER BY create_time DESC limit 3 ");
            list = this.getJdbcTemplate_mall().queryForList(sb.toString(), new Object[]{req.getReqData().get("mer_id")});
            extData.put("goodData", list) ;
            //产品分类
            sb = new StringBuffer();
            sb.append(" SELECT mer_id,id cate_id,`name` cate_name FROM eb_merchant_product_category \n" +
                    " WHERE mer_id = ? AND pid = '0' AND is_show = 1 and is_del = 0 ");
            list = this.getJdbcTemplate_mall().queryForList(sb.toString(), new Object[]{req.getReqData().get("mer_id")});
            ArrayList<Object> mapObj=new ArrayList<Object>();
            Map<String, String> map=new HashMap<String,String>();
            map.put("mer_id",req.getReqData().get("mer_id").toString());
            map.put("cate_id","");
            map.put("cate_name","推荐");
            mapObj.add(0,map);
            for(int i=0;i<list.size();i++){
                map=new HashMap<String,String>();
                map.put("mer_id",list.get(i).get("mer_id").toString());
                map.put("cate_id",list.get(i).get("cate_id").toString());
                map.put("cate_name",list.get(i).get("cate_name").toString());
                mapObj.add(i+1, map);
            }
            extData.put("cateData", mapObj) ;
            //是否显示价格
            sb = new StringBuffer();
            sb.append(" SELECT a.mer_isShowPrize from t_company a where a.mer_id=? ");
            list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{req.getReqData().get("mer_id")});
            if(list.size()>0){
                extData.put("mer_isShowPrize", list.get(0).get("mer_isShowPrize")) ;
            }else{
                extData.put("mer_isShowPrize", "0") ;
            }
            res.setExtData(extData);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getProductList(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取商品列表信息失败");
        try {
            StringBuffer sb = new StringBuffer();
            sb.append(" SELECT a.price,a.mer_id,a.id product_id,a.`name` store_name,a.intro store_info,CONCAT('https://nes-mer.oss-cn-beijing.aliyuncs.com/',a.image) image,a.sales,a.cate_id,b.`name` cate_name " +
                    " from eb_product a " +
                    " LEFT JOIN eb_merchant_product_category b on a.cate_id=b.id " +
                    " where a.mer_id=? ");
            if(!StringUtils.isEmpty((String)req.getReqData().get("keyword"))) {
                sb.append(" and ( ");
                sb.append(" a.name like '%").append(req.getReqData().get("keyword")).append("%' ");
                sb.append(" or a.intro like '%").append(req.getReqData().get("keyword")).append("%' ");
                sb.append(" )");
            }
            if(!StringUtils.isEmpty((String)req.getReqData().get("cate_id"))) {
                sb.append(" and b.pid = '").append(req.getReqData().get("cate_id")).append("' ");
            }
            sb.append(" ORDER BY a.create_time DESC limit 10 ");
            List<Map<String, Object>> list = this.getJdbcTemplate_mall().queryForList(sb.toString(), new Object[]{req.getReqData().get("mer_id")});
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData addBrowse(DataReq req)  throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("新增浏览记录失败");
        Connection conn = null ;
        try {
            conn = DBUtils.getJNDIDBConn();
            String userid = req.getUinfo().getId(); // 用户ID
            String companyid = req.getReqData().get("id").toString(); // 公司自增ID
            //先把原来的浏览记录是否最新改为0
            StringBuffer sb = new StringBuffer();
            sb.append(" update t_company_user_browse set sfzx='0' where userid=? and companyid=? and sfzx='1' ");
            this.update(conn,sb.toString(), new Object[]{userid,companyid});
            //新增浏览记录
            sb.delete(0, sb.length());
            sb.append("insert into t_company_user_browse (userid, companyid) ");
            sb.append("values(?,?)");
            this.update(conn,sb.toString(), new Object[]{userid,companyid});
            conn.commit();
            res.setRetCode(0);
            res.setRetMessage("新增浏览记录成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn!= null) {
                conn.close();
            }
        }
        return res;
    }

    public PageData getProductDetails(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取商品列表信息失败");
        try {
            StringBuffer sb = new StringBuffer();
            String uid = (String)req.getReqData().get("shopUserId"); // 商城用户ID
            if(StringUtils.isEmpty(uid)){
                uid="0000000000";
            }
            sb.append(" SELECT a.*,b.description,c.name mer_name from eb_product a " +
                    " left join eb_product_description b on a.id=b.product_id " +
                    " left join eb_merchant c on a.mer_id=c.id " +
                    " where a.id=? ");
            List<Map<String, Object>> list = this.getJdbcTemplate_mall().queryForList(sb.toString(), new Object[]{req.getReqData().get("product_id")});
            if(list.size()>0){
                String image=(String) list.get(0).get("image");
                list.get(0).put("image","https://nes-mer.oss-cn-beijing.aliyuncs.com/"+image);
                String slider_image=(String) list.get(0).get("slider_image");
                JSONArray arr = (JSONArray) JSON.parse(slider_image);
                String[] slider_arr=new String[arr.size()];
                for(int i=0;i< arr.size();i++){
                    slider_arr[i]="https://nes-mer.oss-cn-beijing.aliyuncs.com/"+arr.get(i);
                }
                list.get(0).put("slider_image",slider_arr);
                String description=(String) list.get(0).get("description");
                description=description.replace("src=\"crmebimage","src=\"https://nes-mer.oss-cn-beijing.aliyuncs.com/crmebimage");
                list.get(0).put("description",description);
            }
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData addRelation(DataReq req)  throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("操作失败");
        try {
            newg_status = begin_newg();
            String uid = (String)req.getReqData().get("shopUserId"); // 商城用户ID
            String product_id = req.getReqData().get("product_id").toString(); // 商品id
            //查询是否有收藏记录
            StringBuffer sb = new StringBuffer();
            sb.append(" select count(*) cnt from eb_product_relation where type='collect' and category='0' and uid=? and product_id=? ");
            int cnt = this.getJdbcTemplate_mall().queryForObject(sb.toString(), new Object[]{uid,product_id}, Integer.class);
            if(cnt>0){//有收藏记录，删除收藏记录
                sb.delete(0, sb.length());
                sb.append(" DELETE FROM eb_product_relation WHERE type='collect' and category='0' and uid=? and product_id=? ");
                this.getJdbcTemplate_mall().update(sb.toString(), new Object[]{uid,product_id});
                res.setKey("0");
            }else{//没有收藏记录添加收藏记录
                sb.delete(0, sb.length());
                sb.append(" insert into eb_product_relation (uid,product_id,type,category) ");
                sb.append(" values(?,?,'collect','0')");
                this.getJdbcTemplate_mall().update(sb.toString(), new Object[]{uid,product_id});
                res.setKey("1");
            }
            commit_newg(newg_status);
            res.setRetCode(0);
            res.setRetMessage("操作成功");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {

        }
        return res;
    }

    public PageData getRelationList(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取商品收藏列表失败");
        try {
            StringBuffer sb = new StringBuffer();
            StringBuffer sbfrom = new StringBuffer();
            String uid = (String)req.getReqData().get("shopUserId"); // 商城用户ID
            sb.append(" SELECT b.price,b.id product_id,b.mer_id,b.`name` store_name,b.`intro` store_info,CONCAT('https://nes-mer.oss-cn-beijing.aliyuncs.com/',b.image) image,b.sales from eb_product_relation a \n" +
                    " LEFT JOIN eb_product b on a.product_id=b.id \n" +
                    " WHERE a.uid=? ");
            sbfrom.append(sb.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            Page page = new Page(this.getJdbcTemplate_mall(),sbfrom.toString(), req.getPageid(), req.getPagesize(), res,new Object[] {uid});
            sb.append(" ORDER BY a.create_time desc ");
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            List<Map<String, Object>> list = this.getJdbcTemplate_mall().queryForList(sb.toString(),new Object[] {uid});
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getUserDals(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取用户信息失败");
        Map<String,Object> extData = new LinkedHashMap<String, Object>();
        try {
            String shopUserId = (String)req.getReqData().get("shopUserId"); // 商城用户ID
            //收藏产品数量
            StringBuffer sb = new StringBuffer();
            sb.append(" select count(*) cnt from eb_product_relation where type='collect' and category='0' and uid=? ");
            int sccnt = this.getJdbcTemplate_mall().queryForObject(sb.toString(), new Object[]{shopUserId}, Integer.class);
            extData.put("sccnt", sccnt) ;
            res.setExtData(extData);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

    public PageData getAreaCodeByLocation(DataReq req) throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("没有查询到行政區劃！");
        StringBuffer sb = new StringBuffer();
        String xzqhdm = null ;
        Map<String, Object> mapData = new LinkedHashMap<String, Object>();
        res.setRetCode(9) ;
        res.setRetMessage("未查询到行政区划信息") ;
        try {
            JSONObject geo = MapUtil.getGeoByLocation((String) req.getReqData().get("location"));
            if (geo != null) {

                String adcode = geo.getString("towncode") ;
                if (StrUtil.isNotEmpty(adcode)) {
                    if (adcode.startsWith("110118")) {
                        adcode = "110128" + adcode.substring(6);
                    }
                    if (adcode.startsWith("110119")) {
                        adcode = "110129" + adcode.substring(6);
                    }
                    mapData.put("decode", adcode) ;
                    xzqhdm =  adcode.substring(0, 9) + "000" ;
                    //查找数据库是否存在该区域
                    sb.append("select  count(1) cnt from t_xzqh where xzqhdm=?  ") ;
                    int cnt = this.getJdbcTemplate_newg().queryForObject(sb.toString(), new Object[] { xzqhdm}, Integer.class) ;
                    if(cnt==0) {
                        xzqhdm =  adcode.substring(0, 6) + "000000" ;
                    }
                }

                if(!StringUtils.isEmpty(xzqhdm)) {
                    sb.delete(0, sb.length()) ;
                    sb.append("select  xzqhmc,xzqhqc,xzqhjb from t_xzqh where xzqhdm=?  ") ;
                    List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{xzqhdm});
                    if(list.size()>0) {
                        mapData.put("xzqhmc",list.get(0).get("xzqhmc")) ;
                        mapData.put("xzqhqc", list.get(0).get("xzqhqc")) ;
                        mapData.put("xzqhjb", list.get(0).get("xzqhjb")) ;
                        mapData.put("xzqhdm", xzqhdm) ;

                        mapData.put("province", geo.getString("province")) ;
                        mapData.put("district", geo.getString("district")) ;
                        mapData.put("township", geo.getString("township")) ;

                        res.setRetCode(0) ;
                        res.setRetMessage("OK") ;
                    }

                }

                String location = geo.getString("location");
                if (StrUtil.isNotEmpty(location)) {
                    final String[] coordinates = location.split(",");
                    mapData.put("longitude", coordinates[0]) ;
                    mapData.put("latitude", coordinates[1]) ;
                }
            }

            res.setExtData(mapData);

        } catch (Exception e) {
            e.printStackTrace();
        }  finally {
        }
        return res;
    }

    public PageData getNextXzqh(DataReq req) throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("没有查询到行政區劃！");
        StringBuffer sb = new StringBuffer();
        String xzqhdm = req.getReqData().get("xzqhdm").toString();
        try {
            if(StringUtils.isEmpty(xzqhdm)) {
                JSONObject geo = MapUtil.getGeoByAddress((String) req.getReqData().get("address"));
                if (geo != null) {
                    String adcode = geo.getString("adcode");
                    if (StrUtil.isNotEmpty(adcode)) {
                        if (adcode.startsWith("110118")) {
                            adcode = "110128" + adcode.substring(6);
                        }
                        if (adcode.startsWith("110119")) {
                            adcode = "110129" + adcode.substring(6);
                        }
                        xzqhdm =  adcode.substring(0, 6) + "000000" ;
                    }
                    String location = geo.getString("location");
                    if (StrUtil.isNotEmpty(location)) {
                        final String[] coordinates = location.split(",");
                        req.getReqData().put("longitude", coordinates[0]);
                        req.getReqData().put("latitude", coordinates[1]);
                    }
                }

                if(StringUtils.isEmpty(xzqhdm)) {
                    xzqhdm = "86" ;
                }
            }

            if("11".equals(xzqhdm)) {
                xzqhdm = "110100000000" ;
            }
            if("12".equals(xzqhdm)) {
                xzqhdm = "120100000000" ;
            }

            sb.append(" SELECT xzqhdm,xzqhmc,xzqhqc from t_xzqh where sjqhdm='").append(xzqhdm).append("' ");
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[] {}) ;
            res.setRetCode(0) ;
            res.setRetMessage("OK") ;
            res.setPage(list) ;
        } finally {

        }
        return res;
    }

}
